package com.sq.partner.manager.dao.impl;

/**
 * 交易记录
 * @author Kang Zhiqiang
 *
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.crypto.Data;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.IGatewayTransDao;
import com.sq.partner.manager.mybatis.model.GatewayTrans;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class GatewayTransDaoImpl extends BaseDaoImpl implements
		IGatewayTransDao {
	private static Logger logger = LogUtil.getLog(GatewayTransDaoImpl.class);

	@SuppressWarnings("unchecked")
	@Override
	public Page queryByCriteria(GatewayTrans trans, GatewayUser user,
			Integer pageIndex) throws Exception {
		
		//String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
		Map<String,Object> paramMap = queryzByCriteriaParam(trans,user,pageIndex);
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		ResultSet rs = null;
		List<String> list = (List<String>) paramMap.get("list");
		List<Boolean> isLike = (List<Boolean>) paramMap.get("isLike");
		try{
			conn = this.getConnection();
			
			String sql=(String) paramMap.get("sql");
			
			if(pageIndex == null || pageIndex == 0){
				pstm1 = conn.prepareStatement(sql);
				for (int i = 0; i < list.size(); i++) {
					if (isLike.get(i)) {
						pstm1.setObject(i + 1, "%" + list.get(i) + "%");
					} else {
						pstm1.setObject(i + 1, list.get(i));
					}
				}
				rs = pstm1.executeQuery();
				List transList = DBUtil.transRs2List(rs);
				
				Page page = new Page();
				page.setData(transList);
				return page;
			}
			
			logger.debug("执行SQL:" + sql);
			pstm = conn.prepareStatement("select count(*) from (" + sql + ")");
			for (int i = 0; i < list.size(); i++) {
				if (isLike.get(i)) {
					pstm.setObject(i + 1, "%" + list.get(i) + "%");
				} else {
					pstm.setObject(i + 1, list.get(i));
				}
			}
			rs = pstm.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			Page page = new Page(pageIndex, total);
			SqlUtil sqlUtil = new SqlUtil();
			String pagesql = sqlUtil.buildPageSqlForOracle(sql, page).toString();
			logger.debug("执行SQL:" + pagesql);
			pstm1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if (isLike.get(i)) {
					pstm1.setObject(i + 1, "%" + list.get(i) + "%");
				} else {
					pstm1.setObject(i + 1, list.get(i));
				}
			}
			rs = pstm1.executeQuery();
			List transList = DBUtil.transRs2List(rs);
			page.setData(transList);
			return page;
		}finally{
			close(rs);
			close(pstm1);
			close(pstm);
			close(conn);
		}
	}
	
	private Map<String,Object> queryzByCriteriaParam(GatewayTrans trans, GatewayUser user,Integer pageIndex){
		Map<String,Object> map = new HashMap<String,Object>();
		List<String> list = new ArrayList<String>();
		List<Boolean> isLike = new ArrayList<Boolean>();
		StringBuffer sqlbuf = new StringBuffer();
		//noted by gzz@2015-05-23 for redmine 136
		/*sqlbuf.append("select t.transid,f.channel_name, to_char(t.trxdate, 'YYYY-mm-DD') as trxDateStr, g.trans_name,"+
		          " t.polno, t.confirmno, t.premium,t.agentfee, to_char(t.start_time, 'YYYY-mm-DD') as startTimeStr,"+
		          " t.check_flag, t.update_time, t.insert_time"+
		    " from t_gateway_trans t,t_gateway_channel_transtype g,"+
		      " (select m.channel_code, m.channel_name"+
		         " from t_gateway_channel_info m where m.dept_code in"+
		              " (select p.dept_code from t_core_dept p where length(p.dept_code) = 2"+
			               " connect by prior p.dept_code = p.snr_dept"+
			                " start with p.dept_code in ('"+deptStr+"'))"+
		          " and m.status = '1'"+
		       " ) f, t_gateway_channel_app kk"+
			" where t.trxcode = g.trans_code"+
			  " and t.channel_code = g.fk_channel_code"+
			  " and t.channel_code = f.channel_code"
			  + " and t.app_id = kk.app_id"
			  + " and t.channel_code = kk.channel_code");*/
		if("00".equalsIgnoreCase(trans.getBranchCode())){
			sqlbuf.append("select t.transid,f.channel_name, to_char(t.trxdate, 'YYYY-mm-DD') as trxDateStr, g.trans_name,"+
			          " t.polno, t.confirmno, t.premium,t.agentfee, to_char(t.start_time, 'YYYY-mm-DD') as startTimeStr,"+
			          " t.check_flag, t.update_time, t.insert_time"+
			          " ,'' showButton"+
			    " from t_gateway_trans t,t_gateway_channel_transtype g,"+
			      " (select m.channel_code, m.channel_name"+
			         " from t_gateway_channel_info m where m.status = '1'"+
			       " ) f, t_gateway_channel_app kk"+
				" where t.trxcode = g.trans_code"+
				  " and t.channel_code = g.fk_channel_code"+
				  " and t.channel_code = f.channel_code"
				  + " and t.app_id = kk.app_id"
				  + " and t.channel_code = kk.channel_code");
		}
		else{
			sqlbuf.append("select t.transid,f.channel_name, to_char(t.trxdate, 'YYYY-mm-DD') as trxDateStr, g.trans_name,"+
			          " t.polno, t.confirmno, t.premium,t.agentfee, to_char(t.start_time, 'YYYY-mm-DD') as startTimeStr,"+
			          " t.check_flag, t.update_time, t.insert_time"+
			          " ,  case when (g.trans_name='投保' and  check_flag in (2,0) )  then '<a href=javascript:void(0) onclick=reconclick(this,1) >已对账</a>&nbsp;<a href=javascript:void(0) onclick=policyCancel(this,1)>保单注销</a>'   when (g.trans_code in ('1022','CIC005') and check_flag in (2,0) ) then '<a  onclick=reconclick(this,2)>已对账</a>'else '' end  showButton"+
			    " from t_gateway_trans t,t_gateway_channel_transtype g,  "
			    + " (select m.dept_code from t_core_dept m where length(m.dept_code)=8 start with dept_code = '"+trans.getBranchCode()+"' connect by nocycle prior m.dept_code = m.snr_dept) dept_tem ,"+
			      " (select m.channel_code, m.channel_name from t_gateway_channel_info m where m.status = '1') f, t_gateway_channel_app kk"+
				" where t.makechannel = dept_tem.dept_code "
				+ " and t.trxcode = g.trans_code"+
				"  and t.app_id=g.app_id "+
				  " and t.channel_code = g.fk_channel_code"+
				  " and t.channel_code = f.channel_code"
				  + " and t.app_id = kk.app_id"
				  + " and t.channel_code = kk.channel_code");
		}
		String sql;
		
		if (StrUtil.isNotEmpty(trans.getMakechannel())) {
			sqlbuf.append(" and t.Makechannel=?");
			list.add(trans.getMakechannel());
			isLike.add(false);
		}
		if (StrUtil.isNotEmpty(trans.getChannelProductcode())) {
			sqlbuf.append(" and t.CHANNEL_PRODUCTCODE=?");
			list.add(trans.getChannelProductcode());
			isLike.add(false);
		}
		if (StrUtil.isNotEmpty(trans.getChannelCode())) {
			sqlbuf.append(" and t.channel_code=?");
			list.add(trans.getChannelCode());
			isLike.add(false);
		}
		if (trans.getCheckFlag() != null) {
			sqlbuf.append(" and t.check_flag=?");
			list.add(trans.getCheckFlag().toString());
			isLike.add(false);
		}
		if (StrUtil.isNotEmpty(trans.getPolno())) {
			sqlbuf.append(" and t.polno like ?");
			list.add(trans.getPolno());
			isLike.add(true);
		}
		if (StrUtil.isNotEmpty(trans.getConfirmno())) {
			sqlbuf.append(" and t.confirmno like ?");
			list.add(trans.getConfirmno());
			isLike.add(true);
		}
		if (StrUtil.isNotEmpty(trans.getTrxdateBegin())) {
			sqlbuf.append(" and t.trxdate >= to_date(?,'yyyy-MM-dd')");
			list.add(trans.getTrxdateBegin());
			isLike.add(false);
		}
		if (StrUtil.isNotEmpty(trans.getTrxdateEnd())) {
			sqlbuf.append(" and t.trxdate < to_date(?,'yyyy-MM-dd')+1");
			list.add(trans.getTrxdateEnd());
			isLike.add(false);
		}
		if (StrUtil.isNotEmpty(trans.getSubPartnerNo())) {
			sqlbuf.append(" and t.CHANNEL_COMCODE=?");
			list.add(trans.getSubPartnerNo());
			isLike.add(false);
		}
		sqlbuf.append(" order by t.update_time desc, t.insert_time desc");
		map.put("list", list);
		map.put("isLike", isLike);
		map.put("sql", sqlbuf.toString());
		
		return map;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Map queryzMapByCriteria(GatewayTrans trans, GatewayUser user,
			Integer pageIndex) throws Exception {
		Map<String,Object> map = new HashMap<String,Object>();
		
		//String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
		Map<String,Object> paramMap = queryzByCriteriaParam(trans,user,pageIndex);
		
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		PreparedStatement pstm2 = null;
		ResultSet rs = null;
		List<String> list = (List<String>) paramMap.get("list");
		List<Boolean> isLike = (List<Boolean>) paramMap.get("isLike");
		try{
			conn = this.getConnection();
			
			String sql=(String) paramMap.get("sql");
			String sql1;
			if(pageIndex == null || pageIndex == 0){
				pstm1 = conn.prepareStatement(sql);
				for (int i = 0; i < list.size(); i++) {
					if (isLike.get(i)) {
						pstm1.setObject(i + 1, "%" + list.get(i) + "%");
					} else {
						pstm1.setObject(i + 1, list.get(i));
					}
				}
				rs = pstm1.executeQuery();
				List transList = DBUtil.transRs2List(rs);
				Page page = new Page();
				page.setData(transList);
				map.put("page", page);
				
				sql1 = "select sum(premium) as allpremium,sum(agentfee) as allagentfee from (" + sql + ")";
				pstm2 = conn.prepareStatement(sql1);
				rs = pstm2.executeQuery();
				rs.next();
				String allpremium= rs.getString("ALLPREMIUM");
				String allagentfee= rs.getString("ALLAGENTFEE");
				map.put("premium", allpremium);
				map.put("agentfee", allagentfee);
				return map;
			}
			
			logger.debug("执行SQL:" + sql);
			pstm = conn.prepareStatement("select count(*) from (" + sql + ")");
			for (int i = 0; i < list.size(); i++) {
				if (isLike.get(i)) {
					pstm.setObject(i + 1, "%" + list.get(i) + "%");
				} else {
					pstm.setObject(i + 1, list.get(i));
				}
			}
			rs = pstm.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			Page page = new Page(pageIndex, total);
			SqlUtil sqlUtil = new SqlUtil();
			String pagesql = sqlUtil.buildPageSqlForOracle(sql, page).toString();
			logger.debug("执行SQL:" + pagesql);
			pstm1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if (isLike.get(i)) {
					pstm1.setObject(i + 1, "%" + list.get(i) + "%");
				} else {
					pstm1.setObject(i + 1, list.get(i));
				}
			}
			rs = pstm1.executeQuery();
			List transList = DBUtil.transRs2List(rs);
			page.setData(transList);
			
			map.put("page", page);
			
			sql1 = "select sum(premium) as allpremium,sum(agentfee) as allagentfee from (" + sql + ")";
			pstm2 = conn.prepareStatement(sql1);
			for (int i = 0; i < list.size(); i++) {
				if (isLike.get(i)) {
					pstm2.setObject(i + 1, "%" + list.get(i) + "%");
				} else {
					pstm2.setObject(i + 1, list.get(i));
				}
			}
			rs = pstm2.executeQuery();
			rs.next();
			DecimalFormat format = new DecimalFormat("#0.00");
			String allpremium= format.format(rs.getDouble("ALLPREMIUM"));
			String allagentfee=format.format(rs.getDouble("ALLAGENTFEE"));
			map.put("premium", allpremium==null||"".equals(allpremium)?"0":allpremium);
			map.put("agentfee", allagentfee==null||"".equals(allagentfee)?"0":allagentfee);
			map.put("sysdata", new Date() );
		}finally {
			close(rs);
			close(pstm1);
			close(pstm2);
			close(pstm);
			close(conn);
		}
		
		return map;
	}
	
	@Override
	public List<GatewayTrans> queryListByTrans(GatewayTrans trans)throws Exception {
		 return  this.queryList("queryGatewayTransByMap", trans);
	}

	@Override
	public List<GatewayTrans> queryListByTransAndDept(GatewayUser user,
			GatewayTrans trans) throws Exception {
		//String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuilder sqlbase = new StringBuilder();
			//noted by gzz@20150817 for fixed bug
			/*sqlbase.append("select t.transid,t.premium,t.check_flag from t_gateway_trans t"+
							" where t.channel_code in"+
											" (select m.channel_code from t_gateway_channel_info m"+
												" where m.dept_code in"+
													" (select p.dept_code from t_core_dept p"+
														" where length(p.dept_code) = 2"+
															" connect by prior p.dept_code = p.snr_dept"+
																" start with p.dept_code in ('"+deptStr+"'))"+
												" and m.status = '1')");*/
			if(user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE)){
				sqlbase.append("SELECT T.TRANSID, T.PREMIUM, T.CHECK_FLAG FROM T_GATEWAY_TRANS T"
						+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1 ON T.CHANNEL_CODE = T1.CHANNEL_CODE ");
			}else{
				sqlbase.append("SELECT T.TRANSID, T.PREMIUM, T.CHECK_FLAG FROM T_GATEWAY_TRANS T INNER JOIN (SELECT DISTINCT P.DEPT_CODE"
						+ " FROM T_CORE_DEPT P WHERE LENGTH(P.DEPT_CODE) = 8 CONNECT BY PRIOR P.DEPT_CODE = P.SNR_DEPT"
						+ " START WITH P.DEPT_CODE IN ("+user.getDepartmentStr()+")) TBL_TEM ON T.MAKECHANNEL = TBL_TEM.DEPT_CODE");
			}
			sqlbase.append(" WHERE INSTR(T.TRANSID, 'Unknown') = 0 ");
			List<String> params = new ArrayList<String>();
			if(StrUtil.isNotEmpty(trans.getTrxcode())){
				sqlbase.append(" and t.trxcode = ?");
				params.add(trans.getTrxcode());
			}
			if(StrUtil.isNotEmpty(trans.getPolno())){
				sqlbase.append(" and t.polno = ?");
				params.add(trans.getPolno());
			}
			if(StrUtil.isNotEmpty(trans.getConfirmno())){
				sqlbase.append(" and t.confirmno = ?");
				params.add(trans.getConfirmno());
			}
			
			pstmt = conn.prepareStatement(sqlbase.toString());
			for (int i = 0;i<params.size();i++) {
				pstmt.setObject(i+1, params.get(i));
			}
			logger.debug("手动对账查询sql："+sqlbase.toString());
			logger.debug("手动对账查询参数："+params);
			rs = pstmt.executeQuery();
			List<GatewayTrans> transList = new ArrayList<GatewayTrans>();
			while(rs.next()){
				GatewayTrans tr = new GatewayTrans();
				tr.setTransid(rs.getString(1));
				tr.setPremium(rs.getBigDecimal(2));
				tr.setCheckFlag(rs.getInt(3));
				
				transList.add(tr);
			}
			return transList;
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
	}

	@Override
	public boolean updateNotNull(GatewayTrans trans) throws Exception {
		return this.update("updateGatewayTransNotNull", trans);
	}

	@Override
	public boolean save(GatewayTrans trans) throws Exception {
		return this.add("insertGatewayTrans", trans);
	}
}
	 

	 

	 

 
